package com.hhit.collaborative_learning.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.hhit.collaborative_learning.dao.CoopTaskDao;
import com.mysql.jdbc.StringUtils;

public class CoopTaskDaoImpl implements CoopTaskDao {

	@Override
	public ResultSet query(Connection con, int start, int end, String find,
			String order, String sort) {
		// TODO Auto-generated method stub
		String sql="SELECT t.*,eu.userNickName as eName,eu.userIcon as eIcon,cu.userNickName as cName,cu.userIcon as cIcon FROM collaborative_learning.coop_task as t left join collaborative_learning.user as eu on eu.userId=t.executor left join collaborative_learning.user as cu on cu.userId=t.executor ";
		if (!StringUtils.isNullOrEmpty(find)) {
			sql += " where " + find+ " ";
		}
		if (!StringUtils.isNullOrEmpty(sort)) {
			sql += " order by " + sort + " ";
			if (!StringUtils.isNullOrEmpty(order)) {
				sql += " " + order;
			}
		}
		if (end != 0 && start < end) {
			sql += " limit " + start + "," + end;
		}
		PreparedStatement prs;
		try {
			prs = con.prepareStatement(sql);
			System.out.println("请求：" + sql);
			return prs.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("非法请求：" + sql);
			return null;
		}
	}

	@Override
	public int insert(Connection con, String Name,String etime, String cuid, String euid,
			String projid) {
		// TODO Auto-generated method stub
		String sql="INSERT INTO `collaborative_learning`.`coop_task`(`Name`,`Creator`,`CreatedOn`,`State`,`executor`,`etime`,`pid`)VALUES(?,?,?,?,?,?,?)";
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			prs.setString(1, Name);
			prs.setInt(2, Integer.valueOf(cuid));
			prs.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			prs.setString(4, "未进行");
			prs.setInt(5, Integer.valueOf(euid));  
			SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm");
			Date date=format.parse(etime);
			prs.setTimestamp(6, new Timestamp(date.getTime()));
			prs.setInt(7,  Integer.valueOf(projid));
			return prs.executeUpdate();
		} catch (SQLException | ParseException ex) {
			ex.printStackTrace();
			return 0;
		} 
	}

	@Override
	public int updateState(Connection con, String find, String state) {
		// TODO Auto-generated method stub
		String sql="UPDATE `collaborative_learning`.`coop_task` SET `State` ='"+state+"' WHERE  "+find;
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			return prs.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
			System.out.println("非法sql语句  "+sql);
			return 0;
		} 
	}

	@Override
	public int delete(Connection con, String find) {
		// TODO Auto-generated method stub
		String sql="DELETE FROM `collaborative_learning`.`coop_task` WHERE "+find;
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			return prs.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
			System.out.println("非法sql语句  "+sql);
			return 0;
		} 
	}

	@Override
	public int updateDesc(Connection con, String find, String desc) {
		// TODO Auto-generated method stub
		String sql="UPDATE `collaborative_learning`.`coop_task` SET `Desc` ='"+desc+"' WHERE  "+find;
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			return prs.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
			System.out.println("非法sql语句  "+sql);
			return 0;
		} 
	}

	@Override
	public int getCount(Connection con, String find) {
		// TODO Auto-generated method stub
		int num = 0;
		String sql = "select count(*) from collaborative_learning.coop_task ";
		if (!StringUtils.isNullOrEmpty(find)) {
			sql += " where " + find + " ";
		}
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			ResultSet res = prs.executeQuery();
			if (res.next()) {
				num = res.getInt(1);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return num;
	}

	@Override
	public ResultSet getUserTaskCount(Connection con,
			String find) {
		// TODO Auto-generated method stub
		String sql="SELECT t.*,u.userNickName,u.userIcon ,count(*) as tasknum FROM collaborative_learning.coop_task as t left join collaborative_learning.user as u on u.userId=t.executor ";
		if (!StringUtils.isNullOrEmpty(find)) {
			sql += " where " + find+ " ";
		}
		sql+=" group by executor ";
		PreparedStatement prs;
		try {
			prs = con.prepareStatement(sql);
			System.out.println("请求：" + sql);
			return prs.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("非法请求：" + sql);
			return null;
		}
	}

}
